package cn.com.ccxe.dao.impl;

import java.util.List;

import cn.com.ccxe.core.entity.Favorite;
import cn.com.ccxe.core.entity.Keyword;
import cn.com.ccxe.dao.DBHandler;
import cn.com.ccxe.dao.FavoriteDao;
import cn.com.ccxe.dao.OperationStatus;
import cn.com.ccxe.dao.parser.FavoriteParser;
import cn.com.ccxe.dao.parser.KeywordParser;

public class FavoriteDaoImpl extends DBHandler implements
		FavoriteDao {

	public OperationStatus add(int websiteTypeId, String userId) {
		String sql = "INSERT INTO C_FAVORITE_WEBSITE ( ID, WEBSITE_TYPE_ID,USER_ID) "+
						"VALUES (SEQ_C_FAVORITE_WEBSITE.NEXTVAL,?,?)" ;
		Object parameters [] = new Object[]{
				websiteTypeId,
				userId
		};
		return insertOrUpdateOrDelete(sql, parameters);
	}

	public OperationStatus del(int websiteId, String userId) {
		String sql = 
					"DELETE C_FAVORITE_WEBSITE "+
					"WHERE USER_ID=? AND WEBSITE_ID=?";
		Object parameters [] = new Object[]{
				websiteId,
				userId
		};
		return insertOrUpdateOrDelete(sql, parameters);
	}

	public List<Favorite> getAllByUser(String userId) {
		String sql = 
			"SELECT A.ID monitorTypeId,A.TYPE type,A.ISSTART isStart,C.ID websiteId, C.PATH path, C.TITLE title, B.ID websiteTypeId,D.ID " +
			"FROM C_MONITOR_TYPE A ,C_WEBSITE_TYPE B ,C_WEBSITE C,C_FAVORITE_WEBSITE D " +
			"WHERE B.ID IN   " +
			"	( " +
			"	  SELECT WEBSITE_TYPE_ID " +
			"	  FROM C_FAVORITE_WEBSITE AA " +
			"	  WHERE USER_ID = ? " +
			"    )" +
			"AND D.WEBSITE_TYPE_ID=B.ID  " +
			"AND D.USER_ID = ?  " +
			"AND A.ID=B.MONITOR_TYPE_ID  " +
			"AND B.WEBSITE_ID=C.ID  " +
			"ORDER BY A.ID"; 
			
		Object parameters [] = new Object[]{
				userId,
				userId
		};
		return queryList(sql, parameters, new FavoriteParser());
	}

	public OperationStatus del(int id) {
		//先删除与之关联的关键字
		String sql1 = 
				"DELETE C_WEBSITE_KEYWORD " +
				"WHERE FAVORITE_ID=?";
		insertOrUpdateOrDelete(sql1, new Object[]{id});
		//删除收藏夹中的数据
		String sql2 = 
			"DELETE C_FAVORITE_WEBSITE "+
			"WHERE id=?";
		
		return insertOrUpdateOrDelete(sql2, new Object[]{id});
	}

	public int getFavoriteId(int websiteTypeId, String userId) {
		String sql = "SELECT ID FROM C_FAVORITE_WEBSITE WHERE WEBSITE_TYPE_ID = ? AND USER_ID =?";
		return queryInt(sql, new Object[]{websiteTypeId,userId});
	}

	public OperationStatus addKeyword(int favoriteId, int keywordId) {
		String sql = 
				"INSERT INTO C_WEBSITE_KEYWORD(FAVORITE_ID, KEYWORD_ID) " +
				" VALUES(?,?)";
		return insertOrUpdateOrDelete(sql, new Object[]{favoriteId,keywordId});
	}

	
	public List<Keyword> getKeywordListIsNot(int monitorTypeId, String userId ,int websiteId) {
		String sql = 
			"SELECT BBB.ID keywordId,BBB.TEXT keywordText " +
			"FROM C_KEYWORD_TYPE AAA " +
			"INNER JOIN C_KEYWORD BBB " +
			"ON AAA.KEYWORD_ID = BBB.ID " +
			"WHERE AAA.MONITOR_TYPE_ID=? " +
			"AND AAA.KEYWORD_ID " +
			"NOT IN " +
			"(" +
				"SELECT B.KEYWORD_ID ID " +
				"FROM C_FAVORITE_WEBSITE A " +
				"INNER JOIN C_WEBSITE_KEYWORD B " +
				"ON A.ID = B.FAVORITE_ID " +
				"INNER JOIN C_WEBSITE_TYPE C " +
				"ON A.WEBSITE_TYPE_ID = C.ID " +
				"WHERE A.USER_ID=? " +
				"AND C.MONITOR_TYPE_ID = ? " +
				"AND C.WEBSITE_ID = ?" +
			")" ;
		Object[] objs = new Object[]{
							monitorTypeId,
							userId,
							monitorTypeId,
							websiteId
						};
		return queryList(sql, objs, new KeywordParser());
	}
	
	public List<Keyword> getKeywordList(int mnitorTypeId, String userId, int websiteId) {
		String sql = 
			"SELECT BBB.ID keywordId,BBB.TEXT keywordText " +
			"FROM C_KEYWORD_TYPE AAA " +
			"INNER JOIN C_KEYWORD BBB " +
			"ON AAA.KEYWORD_ID = BBB.ID " +
			"WHERE AAA.MONITOR_TYPE_ID=? " +
			"AND AAA.KEYWORD_ID " +
			"IN " +
			"(" +
				"SELECT B.KEYWORD_ID ID " +
				"FROM C_FAVORITE_WEBSITE A " +
				"INNER JOIN C_WEBSITE_KEYWORD B " +
				"ON A.ID = B.FAVORITE_ID " +
				"INNER JOIN C_WEBSITE_TYPE C " +
				"ON A.WEBSITE_TYPE_ID = C.ID " +
				"WHERE A.USER_ID=? " +
				"AND C.MONITOR_TYPE_ID = ? " +
				"AND C.WEBSITE_ID = ?" +
			")" ;
		Object[] objs = new Object[]{
				mnitorTypeId,
				userId,
				mnitorTypeId
				,websiteId
		};
		return queryList(sql, objs, new KeywordParser());
	}

	public OperationStatus delKeyword(int favoriteId, int keywordId) {
		String sql = 
			"DELETE C_WEBSITE_KEYWORD T " +
			"WHERE T.FAVORITE_ID=? " +
			"AND T.KEYWORD_ID =?";
		return insertOrUpdateOrDelete(sql, new Object[]{favoriteId,keywordId});
	}
	
	public static void main(String[] args) {
		String sql = 
				"SELECT * " +
				"FROM C_KEYWORD_TYPE AAA " +
				"INNER JOIN C_KEYWORD BBB " +
				"ON AAA.KEYWORD_ID = BBB.ID " +
				"WHERE AAA.MONITOR_TYPE_ID=1 " +
				"AND AAA.KEYWORD_ID " +
				"IN " +
				"(" +
					"SELECT B.KEYWORD_ID ID " +
					"FROM C_FAVORITE_WEBSITE A " +
					"INNER JOIN C_WEBSITE_KEYWORD B " +
					"ON A.ID = B.FAVORITE_ID " +
					"INNER JOIN C_WEBSITE_TYPE C " +
					"ON A.WEBSITE_TYPE_ID = C.ID " +
					"WHERE A.USER_ID='CCX' " +
					"AND C.MONITOR_TYPE_ID = 1 " +
				")" ;
		System.out.println(sql.toUpperCase());
	}

	public OperationStatus addFavoriteKeyword(int keywordId, int moduleId,String userId) {
		String sql = 
			"INSERT INTO C_FAVORITE_KEYWORD(USER_ID, KEYWORD_ID, MODULE_ID) " +
			"VALUES (?,?,?)";
		Object[] objs = new Object[]{
				userId,
				keywordId,
				moduleId
		};
		return insertOrUpdateOrDelete(sql, objs);
	}
	public OperationStatus delFavoriteKeyword(int keywordId, int moduleId,String userId) {
		String sql = 
			"DELETE C_FAVORITE_KEYWORD " +
			"WHERE USER_ID=? AND KEYWORD_ID=? AND MODULE_ID=? ";
			
		Object[] objs = new Object[]{
				userId,
				keywordId,
				moduleId
		};
		return insertOrUpdateOrDelete(sql, objs);
	}

	public List<Keyword> getSelectedKeywordList(int moduleId, String userId) {
		String sql = 
				" SELECT T.ID KEYWORDID, T.TEXT KEYWORDTEXT FROM " +
				" C_KEYWORD T " +
				" WHERE T.ID IN (" +
				"	SELECT A.KEYWORD_ID " +
				"	FROM C_FAVORITE_KEYWORD A" +
				"	WHERE A.MODULE_ID=? AND A.USER_ID = ?" +
				")";
		return queryList(sql, new Object[]{moduleId,userId},new KeywordParser());
	}

	public List<Keyword> getNotSelectedKeywordList(int moduleId, String userId) {
		String sql = 
			" SELECT T.ID KEYWORDID, T.TEXT KEYWORDTEXT FROM " +
			" C_KEYWORD T " +
			" WHERE T.ID NOT IN (" +
			"	SELECT A.KEYWORD_ID " +
			"	FROM C_FAVORITE_KEYWORD A" +
			"	WHERE A.MODULE_ID=? AND A.USER_ID = ?" +
			")";
		return queryList(sql, new Object[]{moduleId,userId},new KeywordParser());
	}

}
